package edu.sju.ssm;

import java.io.File;
import java.io.Reader;
import java.math.BigDecimal;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDate;
import java.util.List;
import java.util.Scanner;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import edu.sju.ssm.entity.Book;

@SuppressWarnings("unused")
public class MyBatisExercise {
	private static final String DB_PATH = "D:/databases/118095a/bookdb";
	private static final String DB_EXT = ".mv.db";
	private static final String SCRIPT_PATH = Thread.currentThread().getContextClassLoader().getResource(".").getPath().toString() + "creation.sql";
	
	private static final String DB_URL = "jdbc:h2:/" + DB_PATH;
	private static final String DB_USER = "sa";
	private static final String DB_PASSWORD = "";
	static SqlSession session=null;
	static Scanner input = new Scanner(System.in);
	
	public static void main(String[] args) throws Exception {
		Reader config = Resources.getResourceAsReader("mybatis-config.xml");
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(config);
		session = sqlSessionFactory.openSession();
		
		name:
		while (true) {
			System.out.println();
			System.out.println("1. 查看所有书籍");
			System.out.println("2. 查找书籍");
			System.out.println("3. 添加书籍");
			System.out.println("4. 修改书籍");
			System.out.println("5. 删除书籍");
			System.out.println("0. 退出");
			System.out.print("请选择：");
			int choice=Integer.parseInt(input.nextLine());
			switch (choice) {
			case 1:
				selectAllBook();
				break;
			case 2:
				selectOneBook();
				break;
			case 3:
				addBook();
				break;
			case 4:
				updateBook();
				break;
			case 5:
				deleteBook();
				break;
			default:
				break name;
			}
		}
		session.close();
	}

	/**
	 * 添加新书籍
	 */
	private static void addBook() {
		System.out.print("书名：");
		String title = input.nextLine();
		System.out.print("出版日期：(格式:0000-00-00)");
		LocalDate datePublished = LocalDate.parse(input.nextLine());
		System.out.print("价格:(格式:00.00)");
		BigDecimal price = new BigDecimal(input.nextLine());
		Book newBook = new Book(title, datePublished, price);
		session.insert("insertBook", newBook);
		System.out.println("书籍已添加！");
		session.commit();
	}
	
	/**
	 * 修改原书籍
	 */
	private static void updateBook() {
		System.out.print("原书籍ID：");
		int bookId = 0;
		bookId = Integer.parseInt(input.nextLine());
		Book book = session.selectOne("selectBook",bookId);
		if (book == null) {
			System.out.println("找不到书籍");
			return;
		}
		System.out.print("1.改Title：");
		System.out.print("2.改日期：");
		System.out.print("3.改价格：");
		System.out.print("请选择：");
		int choice=Integer.parseInt(input.nextLine());
		switch (choice) {
		case 1:
			System.out.print("新书名：");
			String title = input.nextLine();
			book.setTitle(title);
			break;
		case 2:
			System.out.print("新出版日期：(格式:0000-00-00)");
			LocalDate datePublished = LocalDate.parse(input.nextLine());
			book.setDatePublished(datePublished);
			break;
		default:
			System.out.print("新价格:(格式:00.00)");
			BigDecimal price = new BigDecimal(input.nextLine());
			book.setPrice(price);
			break;
		}
		
		session.update("updateBook", book);
		session.commit();		
	}
	
	/**
	 * 查询所有书籍
	 */
	private static void selectAllBook() {
		List<Book> allBooks = session.selectList("selectAll");
		for (Book book : allBooks) {
			System.out.println(book);
		}
	}
	
	/**
	 * 删除书籍
	 */
	private static void deleteBook() {
		System.out.print("书籍ID：");
		int bookId = 0;
		bookId = Integer.parseInt(input.nextLine());
		session.delete("deleteBook", bookId);
		System.out.println("书籍已删除！");
		session.commit();
	}
	
	/**
	 * 查询一本书籍
	 */
	private static void selectOneBook() {
		System.out.print("1.按Title查找：");
		System.out.print("2.按ID查找：");
		System.out.print("请选择：");
		int choice=Integer.parseInt(input.nextLine());
		switch (choice) {
		case 1:
			selectBookByTitle();
			break;
		default:
			selectBookById();
			break;
		}
	}
	
	/**
	 * 按Title查询书籍
	 */
	private static void selectBookByTitle() {
		System.out.print("Title：");
		String title = input.nextLine();
		Book book = session.selectOne("selectBookByTitle",title);
		if (book == null) {
			System.out.println("找不到书籍");
			return;
		}
		System.out.println(book);
	}
	
	/**
	 * 按Id查询书籍
	 */
	private static void selectBookById() {
		System.out.print("ID：");
		int bookId = 0;
		bookId = Integer.parseInt(input.nextLine());
		Book book = session.selectOne("selectBook",bookId);
		if (book == null) {
			System.out.println("找不到书籍");
			return;
		}
		System.out.println(book);
	}
//-------------------------------------------------------------
	private static void jdbcSelect() throws ClassNotFoundException, SQLException {
		Class.forName("org.h2.Driver");
		
		try (Connection cn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
				Statement stmt = cn.createStatement();
				ResultSet rs = stmt.executeQuery("select id, title, datePublished, price from book")) {
			
			while (rs.next()) {
				int id = rs.getInt(1);
				String title = rs.getString(2);
				LocalDate publishedDate = rs.getDate(3).toLocalDate();
				BigDecimal price = rs.getBigDecimal(4);//与钱有关的，防止浮点型的不精确
				
				System.out.printf("%d: %s, %s, %.2f%n", id, title, publishedDate, price);
			}
		}
	}
	
	private static void createDB() {
		deleteDbFile();
		
		if (!dbExists()) {
			initDB();
		}
		
		System.out.println("Database initialized.");
	}

	private static boolean dbExists() {
		File file = new File(dbPath());
		return file.exists();
	}
	
	private static void deleteDbFile() {
		File file = new File(dbPath());
		if (file.exists()) {
			file.delete();
		}
	}

	private static void initDB() {
		try {
			org.h2.tools.RunScript.execute("jdbc:h2://" + DB_PATH, "sa", "", SCRIPT_PATH, StandardCharsets.UTF_8, false);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}	
	}
	
	private static String dbPath() {
		return DB_PATH + DB_EXT;
	}
}
